<?php
/**
 * @author Wei Ma
 * @copyright 2014 pipahr.com
 */

defined('_JEXEC') or die;
require_once(OC::$SERVERROOT . "/apps/reports/lib/helper.php");

/**
 * Registration controller class for Users.
 */
class PReportsModelReports extends JModelLegacy
{
   /**
    * Get company summary reports.
    *
    * @param   integer $user_id  Company ID.
    * @return  array   Summary info.
    *
    * @since   3.3
    */
   public function getSummary($user_id = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');
      $datetype = JRequest::getString('datetype', '');

      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;

      $summary = array();
      $summary['date'] = $this->getDateSummary($start, $end, $datetype, $user_id, 0);

      list($source, $resumeParse) = $this->getResumeDetailsData($start, $end, $user_id, 0);
      $summary['source'] = $source;
      $summary['resumeParse'] = $resumeParse;

      return $summary;
   }

   /**
    * Get date lines summary reports.
    *
    * @return  array   Date summary info.
    *
    * @since   3.3
    */
   public function getDateSummary($start = '', $end = '', $datetype = '', $user_id = 0, $jobid = 0) {
      $start = !empty($start) ? $start : JRequest::getString('start', '');
      $end = !empty($end) ? $end : JRequest::getString('end', '');
      $datetype = !empty($datetype) ? $datetype : JRequest::getString('datetype', '');
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $jobid = !empty($jobid) ? $jobid : JRequest::getInt('jobid', 0);

      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      $db = PFactory::getDbo();
      $xtypes = array('resume', 'event', 'offer', 'entry');

      // get summary total
      $date = array();
      $date['total'] = array(
         'job' => 0,
         'resume' => 0,
         'dupResume' => 0,
         'event' => 0,
         'offer' => 0,
         'entry' => 0
      );
      $subSql = !empty($jobid) ? (" AND job_id=" . intval($jobid)) : "";
      $jobSql = $this->getJobRangeSql();
      $query = "SELECT COUNT(DISTINCT email) AS count, 'resume' AS xtype FROM #__jobs_report_resume WHERE user_id=" . $user_id . "
                AND date_added>=" . $db->quote($start) . " AND date_added<=" . $db->quote($end) . " AND date_recorded >=" . $db->quote($start) . " AND date_recorded <=" . $db->quote($end) . $subSql . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . "
                UNION ALL
                SELECT COUNT(DISTINCT resume_id) AS count, 'dupResume' AS xtype FROM #__jobs_report_resume WHERE user_id=" . $user_id . " 
                AND date_added>=" . $db->quote($start) . " AND date_added<=" . $db->quote($end) . " AND date_recorded >=" . $db->quote($start) . " AND date_recorded <=" . $db->quote($end) . $subSql . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . "
                UNION ALL
                SELECT SUM(count) AS count, xtype FROM #__jobs_report_summary WHERE user_id=" . $user_id . " AND date >=" . $db->quote($start) . " AND date <= " . $db->quote($end) . $subSql . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " GROUP BY xtype";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $date['total'] = array_merge($date['total'], $db->loadAssocList('xtype', 'count'));

      // get summary lines data
      $date['lines'] = $this->getDateLinesData($start, $end, $datetype, $user_id, $jobid, $xtypes);
      $date['lines']['data'] = array_values($date['lines']['data']);

      return $date;
   }

   /**
    * Get resumes source and parse data reports.
    *
    * @return  array   Resumes summary info.
    *
    * @since   3.3
    */
   public function getResumeDetailsData($start = '', $end = '', $user_id = 0, $jobid = 0) {
      $start = !empty($start) ? $start : JRequest::getString('start', '');
      $end = !empty($end) ? $end : JRequest::getString('end', '');
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $jobid = !empty($jobid) ? $jobid : JRequest::getInt('jobid', 0);

      if (empty($start) || empty($end)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      $sourceColors = array(
         'upload'  => '#75ceff',
         '51job'   => '#ffa84a',
         'zhilian' => '#84aaff',
         'lagou'   => '#239c7d',
         'jiancai' => '#e46562'
      );

      $db = PFactory::getDbo();
      $jobSql = $this->getJobRangeSql();

      // get source summary data
      $source = array();
      $piesTotal = array();
      $query = "SELECT COUNT(DISTINCT email) AS count, source, 'resume' AS xtype FROM #__jobs_report_resume AS jrr
                WHERE jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobid) ? " AND jrr.job_id=" . intval($jobid) : "") . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . " GROUP BY source
                UNION ALL SELECT SUM(count) AS count, source, xtype FROM #__jobs_report_sources WHERE xtype IN ('event', 'offer', 'entry') AND user_id=" . $user_id . " AND date >=" . $db->quote($start) . " AND date <= " . $db->quote($end) . (!empty($jobid) ? " AND job_id=" . intval($jobid) : "") . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " GROUP BY source, xtype";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $sourceTotal = $db->loadAssocList();
      if (!empty($sourceTotal)) {
         foreach ($sourceTotal as $s) {
            if (!array_key_exists($s['source'], $sourceColors))
               continue;
            $source['total'][$s['source']][$s['xtype']]['count'] = $s['count'];
            $piesTotal[$s['xtype']][$s['source']] = $s['count'];
         }
      }
      if (!empty($piesTotal)) {
         foreach ($piesTotal as $x => $xdata) {
            $xsum = array_sum($xdata);
            if (!empty($xdata)) {
               foreach ($xdata as $s => $c) {
                  $weight = ($xsum > 0 ? round(($c/$xsum)*100, 1) : 0) . '%';
                  $source['total'][$s][$x]['weight'] = $weight;
                  $source['pies'][$x]['data'][] = array($s, intval($c));
                  $source['pies'][$x]['labels'][] = $weight;
                  $source['pies'][$x]['colors'][] = $sourceColors[$s];
               }
            }
         }
      }

      // get resume parse data
      $resumeParse = array();
      $donutsTotal = array();
      $resumeParseColors = array();
      $commonColors = array('#c0b8b8', '#ea8a4d', '#67b4c6', '#a6ca68', '#f073e0', '#c75754', '#764bc5');
      $resumeParseList = array(
         'match_degree' => array('20%以下/其他', '20%-50%', '50%-75%', '75%-100%'),
         'degree_level' => array('初中及以下', '高中/中专', '大专', '本科', '硕士及以上', '其他'),
         'workexp' => array('应届毕业生', '不满1年', '1-2年', '2-5年', '5-10年', '10年以上', '其他'),
         'target_salary' => array('2000元以下', '2000-5000元', '5000-8000元', '8000-10000元', '10000元以上'),
         'age' => array('25岁以下', '25-30岁', '30-35岁', '35-40岁', '40-50岁', '50岁以上', '其他'),
         'sex' => array('男', '女')
      );
      foreach ($resumeParseList as $wtype => $wtexts) {
         foreach ($wtexts as $i => $wtext) {
            $resumeParseColors[$wtype][$wtext] = $commonColors[$i];
         }
      }
      $query = "SELECT SUM(count) AS count, weight_type AS wtype, xtype, weight_text AS wtext FROM #__jobs_report_resumedetails WHERE user_id=" . $user_id . " AND date >=" . $db->quote($start) . " AND date <= " . $db->quote($end) . (!empty($jobid) ? " AND job_id=" . intval($jobid) : "") . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " GROUP BY weight_type, xtype, weight_text";
      foreach ($resumeParseList as $wtype => $v) {
         $query .= " UNION ALL SELECT COUNT(DISTINCT email) AS count, " . $db->quote($wtype) . " AS wtype, 'resume' AS xtype, $wtype AS wtext FROM
                        (SELECT jrr.* FROM #__jobs_report_resume AS jrr
                         LEFT JOIN #__jobs_report_resume AS jrr1 ON jrr1.resume_id = jrr.resume_id AND jrr.id < jrr1.id AND jrr1.date_added >=" . $db->quote($start) . " AND jrr1.date_added <=" . $db->quote($end) . " AND jrr1.date_recorded >=" . $db->quote($start) . " AND jrr1.date_recorded <=" . $db->quote($end) . "
                         WHERE jrr1.resume_id IS NULL AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobid) ? " AND jrr.job_id=" . intval($jobid) : "") . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . ") AS r WHERE $wtype != '' GROUP BY $wtype";
      }
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $parseDetails = $db->loadAssocList();
      if (!empty($parseDetails)) {
         foreach ($parseDetails as $p) {
            $resumeParse['total'][$p['wtype']][$p['wtext']][$p['xtype']]['count'] = $p['count'];
            $donutsTotal[$p['wtype']][$p['xtype']][$p['wtext']] = $p['count'];
         }
      }
      if (!empty($donutsTotal)) {
         foreach ($donutsTotal as $wtype => $wdata) {
            if (!empty($wdata)) {
               foreach ($wdata as $x => $xdata) {
                  $xsum = array_sum($xdata);
                  if (!empty($xdata)) {
                     foreach ($xdata as $wtext => $c) {
                        $weight = ($xsum > 0 ? round(($c/$xsum)*100, 1) : 0) . '%';
                        $resumeParse['total'][$wtype][$wtext][$x]['weight'] = $weight;
                        $resumeParse['donuts'][$wtype][$x]['data'][] = array($wtext, intval($c));
                        $resumeParse['donuts'][$wtype][$x]['colors'][] = $resumeParseColors[$wtype][$wtext];
                        $resumeParse['donuts'][$wtype][$x]['labels'][] = $weight;
                     }
                  }
               }
            }
         }
      }

      return array($source, $resumeParse);
   }

   /**
    * Get company date line summary Reports.
    *
    * @return  array   Date lines summary info.
    *
    * @since   3.3
    */
   public function getDateLinesData($start = '', $end = '', $datetype = '', $user_id = 0, $jobid = 0, $xtypes = array('resume'), $total = false, $contrast = false) {
      $start = !empty($start) ? $start : JRequest::getString('start', '');
      $end = !empty($end) ? $end : JRequest::getString('end', '');
      $datetype = !empty($datetype) ? $datetype : JRequest::getString('datetype', '');
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $jobid = !empty($jobid) ? $jobid : JRequest::getInt('jobid', 0);

      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $today = JFactory::getDate('today')->format('Ymd');
      $startObj = new DateTime($start);
      $endObj = new DateTime($end);
      $endObj->modify('+1 day');
      $diffDays = $startObj->diff($endObj)->format('%a');

      $lines = array();
      $gkeys = array();
      $rkeys = array();
      $jkeys = array();

      if (!$total) {
         $rkeysArr = array(
            'year'    => 'year(date_added)',
            'quarter' => 'quarter(date_added)',
            'month'   => 'month(date_added)',
            'day'     => 'day(date_added)'
         );
      } else {
         $rkeysArr = array(
            'year'    => 'year(date_recorded)',
            'quarter' => 'quarter(date_recorded)',
            'month'   => 'month(date_recorded)',
            'day'     => 'day(date_recorded)'
         );
      }

      $ticks = array();  // xaxis ticks
      $xticks = array(); // data ticks
      switch ($datetype) {
         case 'quarter':
         case 'year':
            array_push($gkeys, 'year', 'month');
            $format = 'Ym';
            $interval = new DateInterval('P1M');
            if ($contrast) {
               if ($datetype == 'quarter') {
                  $endObj = (new DateTime($start))->modify('+3 month');
                  $dateRange = new DatePeriod($startObj, $interval ,$endObj);
                  foreach ($dateRange as $i => $date) {
                     $t_str = '月度' . ($i+1);
                     if ($date->format('Ymd') < $today)
                        $ticks[$date->format($format)] = $t_str;
                     $xticks[] = $t_str;
                  }
                  break;
               } elseif ($datetype == 'year') {
                  $endObj = (new DateTime($start))->modify('+12 month');
               }
            }
            $dateRange = new DatePeriod($startObj, $interval ,$endObj);
            foreach ($dateRange as $date) {
               $t_str = $date->format('m') . '月';
               if ($date->format('Ymd') < $today)
                  $ticks[$date->format($format)] = $t_str;
               $xticks[] = $t_str;
            }
            break;
         case 'month':
            array_push($gkeys, 'year', 'month', 'day');
            $format = 'Ymd';
            $interval = new DateInterval('P1D');
            $dateRange = new DatePeriod($startObj, $interval ,$endObj);
            foreach ($dateRange as $date) {
               $t_str = $date->format('d');
               if ($date->format('Ymd') < $today)
                  $ticks[$date->format($format)] = $t_str;
               $xticks[] = $t_str;
            }
            break;
         case 'custom':
            if ($diffDays <= 32) {
               array_push($gkeys, 'year', 'month', 'day');
               $format = 'Ymd';
               $interval = new DateInterval('P1D');
               $dateRange = iterator_to_array(new DatePeriod($startObj, $interval ,$endObj));
               foreach ($dateRange as $i => $date) {
                  $t_str = '';
                  if ($i > 0) {
                     $lastDate = $dateRange[$i-1];
                     if ($date->format('Y') != $lastDate->format('Y'))
                        $t_str .= $date->format('Y') . '年';
                     if ($date->format('m') != $lastDate->format('m'))
                        $t_str .= $date->format('m') . '月';
                  }
                  $t_str .= $date->format('d') . '日';
                  if ($date->format('Ymd') < $today)
                     $ticks[$date->format($format)] = $t_str;
                  $xticks[] = $t_str;
               }
            } elseif ($diffDays <= 365) {
               array_push($gkeys, 'year', 'month');
               $format = 'Ym';
               $interval = new DateInterval('P1M');
               $dateRange = iterator_to_array(new DatePeriod($startObj, $interval ,$endObj));
               foreach ($dateRange as $i => $date) {
                  $t_str = '';
                  if ($i > 0) {
                     $lastDate = $dateRange[$i-1];
                     if ($date->format('Y') != $lastDate->format('Y'))
                        $t_str .= $date->format('Y') . '年';
                  }
                  $t_str .= $date->format('m') . '月';
                  if ($date->format('Ymd') < $today)
                     $ticks[$date->format($format)] = $t_str;
                  $xticks[] = $t_str;
               }
            } else {
               array_push($gkeys, 'year');
               $format = 'Y';
               $interval = new DateInterval('P1Y');
               $dateRange = new DatePeriod($startObj, $interval ,$endObj);
               foreach ($dateRange as $date) {
                  $t_str = $date->format('Y') . '年';
                  if ($date->format('Ymd') < $today)
                     $ticks[$date->format($format)] = $t_str;
                  $xticks[] = $t_str;
               }
            }
            break;
         default:
            break;
      }

      if (empty($xtypes) || empty($gkeys) || empty($ticks)) {
         $this->setError('系统错误');
         return false;
      }

      foreach ($gkeys as $g) {
         $rkeys[] = $rkeysArr[$g];
      }

      $jkeysArr = array(
         'year'    => 'year(publish_date)',
         'quarter' => 'quarter(publish_date)',
         'month'   => 'month(publish_date)',
         'day'     => 'day(publish_date)'
      );
      foreach ($gkeys as $g) {
         $jkeys[] = $jkeysArr[$g];
      }

      $db = PFactory::getDbo();
      $lines['ticks'] = array_values($xticks);
      $fakeXtyes = array('event', 'offer', 'entry');
      $fakeXtyes = array_intersect($fakeXtyes, $xtypes);
      if (!empty($fakeXtyes)) {
         foreach ($fakeXtyes as $i => $x) {
            $fakeXtyes[$i] = $db->quote($x);
         }
      }
      
      $allSql = array();
      $jobSql = $this->getJobRangeSql();
      if (!$total) {
         if (in_array('job', $xtypes))
            $allSql[] = "SELECT COUNT(DISTINCT job_id) AS count, 'job' AS xtype, publish_date AS date FROM #__jobs_report_job 
                         WHERE system=0 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY " . implode(',', $jkeys);

         if (in_array('resume', $xtypes))
            $allSql[] = "SELECT COUNT(DISTINCT email) AS count, 'resume' AS xtype, date_added AS date FROM 
                           (SELECT jrr.* FROM #__jobs_report_resume AS jrr
                            LEFT JOIN #__jobs_report_resume AS jrr1 ON jrr1.resume_id = jrr.resume_id AND jrr.id < jrr1.id AND jrr1.date_added >=" . $db->quote($start) . " AND jrr1.date_added <=" . $db->quote($end) . " AND jrr1.date_recorded >=" . $db->quote($start) . " AND jrr1.date_recorded <=" . $db->quote($end) . "
                            WHERE jrr1.resume_id IS NULL AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . ") AS r WHERE 1=1 " .
                            (!empty($jobid) ? " AND job_id=" . intval($jobid) : "") . " GROUP BY " . implode(',', $rkeys);
         if (!empty($fakeXtyes))
            $allSql[] = "SELECT SUM(count) AS count, xtype, date FROM #__jobs_report_summary
                         WHERE xtype IN (" . implode(',', $fakeXtyes) . ") AND user_id=" . $user_id . " AND date >=" . $db->quote($start) . " AND date <= " . $db->quote($end) . 
                         (!empty($jobid) ? " AND job_id=" . intval($jobid) : "") . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " GROUP BY xtype, " . implode(',', $gkeys);
      } else {
         if (in_array('resume', $xtypes))
            $allSql[] = "SELECT rjj.resume_total AS count, 'resume' AS xtype, date_recorded AS date FROM 
                         (SELECT * FROM #__jobs_report_job
                          WHERE system=0 AND user_id=" . $user_id . " AND date_recorded>=(SELECT IF(MAX(date_recorded), MAX(date_recorded), '0000-00-00 00:00:00') FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=".$db->quote($start).") AND date_recorded<=" . $db->quote($end) . " AND job_id=" . intval($jobid) . " ORDER BY date_recorded DESC) AS rjj
                         GROUP BY " . implode(',', $rkeys);
         if (in_array('job', $xtypes))
            $allSql[] = "SELECT count, 'job' AS xtype, date_recorded AS date FROM 
                         (SELECT count(DISTINCT job_id) AS count, date_recorded FROM #__jobs_report_job
                          WHERE system=0 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "")  . " AND date_recorded>=(SELECT IF(MAX(date_recorded), MAX(date_recorded), '0000-00-00 00:00:00') FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=".$db->quote($start).") AND date_recorded<=" . $db->quote($end) . " GROUP BY date_recorded ORDER BY date_recorded DESC) AS rjj
                         GROUP BY " . implode(',', $rkeys);
      }
      $query = implode(' UNION ALL ', $allSql);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $details = $db->loadAssocList();

      $linesData = array();
      if (!empty($details)) {
         foreach ($details as $d) {
            $date = new DateTime($d['date']);
            // for total line, we must have first date point data
            if (empty($ticks[$date->format($format)])) {
               if ($total && $startObj->format($format) < $today)
                  $linesData[$d['xtype']][$ticks[$startObj->format($format)]] = intval($d['count']);
            }
            else
               $linesData[$d['xtype']][$ticks[$date->format($format)]] = intval($d['count']);
         }
         if ($total) {
            // set every date point
            if (!empty($linesData)) {
               foreach ($linesData as $x => $xdata) {
                  if (!empty($xdata)) {
                     $lastCount = 0;
                     foreach ($ticks as $date => $short) {
                        if (!isset($xdata[$short])) {
                           if ($date < $today)
                              $xdata[$short] = $lastCount;
                        } else
                           $lastCount = $xdata[$short];
                     }
                     $linesData[$x] = $xdata;
                  }
               }
            }
         }
      }

      foreach ($xtypes as $xtype) {
         foreach ($ticks as $t) {
            if (!isset($linesData[$xtype][$t]))
               $lines['data'][$xtype][$t] = 0;
            else
               $lines['data'][$xtype][$t] = $linesData[$xtype][$t];
         }
      }

      $ynum = 5;
      $max = $ynum;
      foreach ($lines['data'] as $xtype => $array) {
         $lines['data'][$xtype] = array_values($array);
         $amax = max($array);
         $max = $max > $amax ? $max : $amax;
      }
      $lines['tickInterval'] = ceil(($max*1.1)/$ynum);
      $lines['max'] = $lines['tickInterval']*$ynum;

      // if ($contrast)
      //    return $lines;
      // $lines['data'] = array_values($lines['data']);

      return $lines;
   }

   /**
    * Get company job summary Reports.
    *
    * @param   integer $user_id  Company ID.
    * @return  array   Job summary info.
    *
    * @since   3.3
    */
   public function getJobSummary($user_id = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');
      $datetype = JRequest::getString('datetype', '');

      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $config = PFactory::getConfig();
      $statime = PFactory::getDate($start, $config->get('offset'))->toSql();
      $endtime = PFactory::getDate($end . ' 23:59:59', $config->get('offset'))->toSql();

      $summary = array();
      $summary['details'] = array(
         'interview_setup' => 0,
         'onlinetest_setup' => 0,
         'persontest_setup' => 0
      );
      $sourceColors = array(
         'pipapai'  => '#75ceff',
         '51job'   => '#ffa84a',
         'zhilian' => '#84aaff',
         'lagou'   => '#239c7d',
         'jiancai' => '#e46562'
      );

      $db = PFactory::getDbo();
      $jobSql = $this->getJobRangeSql();

      // get job total
      $query = "SELECT COUNT(DISTINCT job_id) AS count, 'totaljob' AS type FROM #__jobs_report_job WHERE system=0 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ")
                UNION ALL
                SELECT COUNT(DISTINCT job_id) AS count, 'newjob' AS type FROM #__jobs_report_job WHERE system=0 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ")
                AND publish_date>=" . $db->quote($start) . "";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $summary['date'] = $db->loadAssocList('type', 'count');
      
      //$summary['date']['lines'] = $this->getDateLinesData($start, $end, $datetype, $user_id, 0, array('job'), true);
      $linesDatas = array($this->getDateLinesData($start, $end, $datetype, $user_id, 0, array('job'), true), $this->getDateLinesData($start, $end, $datetype, $user_id, 0, array('job'), false));
      $summary['date']['lines']['ticks'] = $linesDatas[1]['ticks'];
      foreach ($linesDatas as $linesData) {
         foreach ($linesData['data'] as $x => $data) {
            $summary['date']['lines']['data'][] = $data;
         }
      }
      $ynum = 5;
      $max = $ynum;
      foreach ($summary['date']['lines']['data'] as $d) {
         $amax = max($d);
         $max = $max > $amax ? $max : $amax;
      }
      $summary['date']['lines']['tickInterval'] = ceil(($max*1.1)/$ynum);
      $summary['date']['lines']['max'] = $summary['date']['lines']['tickInterval']*$ynum;

      // get job summary bars data
      $setypeSql = array();
      foreach ($summary['details'] as $setype => $v) {
         $setypeSql[] = "SELECT COUNT(DISTINCT job_id) AS count, " . $db->quote($setype) . " AS setype FROM #__jobs_report_job WHERE system=0 AND $setype=1 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ")";
      }
      $db->setQuery(PUtil::replaceOCPrefix(implode(' UNION ALL ', $setypeSql)));
      $details = $db->loadAssocList('setype', 'count');
      JArrayHelper::toInteger($details);
      $summary['details'] = array_merge($summary['details'], $details);
      $summary['bars']['data'] = array_values($summary['details']);
      $ynum = 5;
      $max = $ynum;
      $amax = max($summary['bars']['data']);
      $max = $max > $amax ? $max : $amax;
      $summary['bars']['tickInterval'] = ceil(($max*1.2)/$ynum);
      $summary['bars']['max'] = $summary['bars']['tickInterval']*$ynum;
      if (!empty($summary['details'])) {
         $setsum = $summary['date']['totaljob'];
         foreach ($summary['details'] as $setype => $c) {
            $summary['details'][$setype] = array(
               'count' => $c,
               'weight' => ($setsum > 0 ? round(($c/$setsum)*100, 1) : 0) . '%'
            );
         }
      }

      // get job source summary pie data
      $pie = array();
      $query = "SELECT COUNT(DISTINCT job_id) AS count, source FROM #__jobs_report_job WHERE system=0 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY source";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $sourceDetails = $db->loadAssocList('source', 'count');
      if (!empty($sourceDetails)) {
         $sum = array_sum($sourceDetails);
         foreach ($sourceDetails as $s => $c) {
            $weight = ($sum > 0 ? round(($c/$sum)*100, 1) : 0) . '%';
            $summary['source']['total'][$s]['count'] = intval($c);
            $summary['source']['total'][$s]['weight'] = $weight;
            if (!empty($sourceColors[$s])) {
               $pie['data'][] = array($s, intval($c));
               $pie['colors'][] = $sourceColors[$s];
               $pie['labels'][] = $weight;
            }
         }
      }

      $summary['source']['pie'] = $pie;
      
      return $summary;
   }

   /**
    * Get company job list reports.
    *
    * @return  array  Job array list
    *
    * @since   3.3
    */
   public function getJobList($user_id = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');

      if (empty($start) || empty($end)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $config = PFactory::getConfig();
      $statime = PFactory::getDate($start, $config->get('offset'))->toSql();
      $endtime = PFactory::getDate($end . ' 23:59:59', $config->get('offset'))->toSql();

      $db = PFactory::getDbo();
      $jobSql = $this->getJobRangeSql();

      // get job list
      // report job table has duplicate data (different source), we must group first
      $jobTable = "(SELECT job_id, job_title, user_id FROM #__jobs_report_job
                    WHERE system=0 AND user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id)";
      $query = "SELECT SQL_CALC_FOUND_ROWS job_id, job_title, GROUP_CONCAT(xtype) AS xtypes, GROUP_CONCAT(count) AS counts FROM 
                ((SELECT COUNT(DISTINCT jrr.email) AS count, rjj.job_id, rjj.job_title, 'resume' AS xtype FROM " . $jobTable . " AS rjj
                  LEFT JOIN #__jobs_report_resume AS jrr ON (jrr.job_id=rjj.job_id AND jrr.user_id=rjj.user_id AND jrr.date_added>=" . $db->quote($start) . " AND jrr.date_added<=" . $db->quote($end) . " AND jrr.date_recorded>=" . $db->quote($start) . " AND jrr.date_recorded<=" . $db->quote($end) . ")
                  GROUP BY rjj.job_id)
                UNION ALL 
                   (SELECT SUM(jrj.count) AS count, rjj.job_id, rjj.job_title, jrj.xtype FROM " . $jobTable . " AS rjj
                   LEFT JOIN #__jobs_report_jobdetails AS jrj ON jrj.job_id=rjj.job_id
                   WHERE jrj.gtype='interview' AND jrj.user_id=" . $user_id . " AND jrj.date>=" . $db->quote($start) . " AND jrj.date<=" . $db->quote($end) . " GROUP BY rjj.job_id, jrj.xtype)) AS j GROUP BY j.job_id ORDER BY j.job_id DESC";

      $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
      // Get the pagination request variables
      $app = PFactory::getApplication();
      $limit = $app->getUserStateFromRequest('reports.limit', 'limit', 15, 'int');
      $limit = $limit > 100 ? 100 : $limit;
      // In case limit has been changed, adjust limitstart accordingly
      $limitstart = ($limit != 0 ? (floor( $limitstart / $limit ) * $limit) : 0);
      /* try to load from cache */
      $cache = CacheHelper::getCache('app_reports');
      // get cached data now
      list($list, $total) = $cache->call(array($this, '_getJobList'), $query, intval($limitstart), intval($limit));
      // Event Driven Cache/Object Caching
      // there are the tables which we need to invoke cache cleaning triggers when tables are changed by user - Event Driven Cache/Object Caching
      CacheHelper::setTriggers($user_id, $cache, array('#__jobs_job', '#__jobs_report_resume', '#__jobs_report_jobdetails'));
      // generate html output now
      $pagination = new PPagination($total, $limitstart, $limit);

      if (!empty($list)) {
         foreach ($list as $j => $l) {
            $xtypes = explode(',', $l['xtypes']);
            $counts = explode(',', $l['counts']);
            if (!empty($l['xtypes'])) {
               foreach ($xtypes as $i => $x) {
                  $list[$j]['total'][$x] = !empty($counts[$i]) ? $counts[$i] : 0;
               }
            }
         }
      }
   
      return array($list, $pagination);
   }

   public function _getJobList($query, $limitstart=0, $limit=0) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query), $limitstart, $limit);
      $result = $db->loadAssocList();
       
      $db->setQuery($db->useLastConnection('SELECT FOUND_ROWS();'));
      $total = $db->loadResult();
      return array($result, $total);
   }

   public function getJobTitle($user_id = 0, $jobid = 0) {
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $jobid = !empty($jobid) ? $jobid : JRequest::getInt('jobid', 0);

      $db = PFactory::getDbo();
      $query = "SELECT job_title FROM #__jobs_job WHERE employer_id=" . $user_id . " AND id=" . intval($jobid);
      $db->setQuery($query);
      return $db->loadResult();
   }

   /**
    * Get company job details Reports.
    *
    * @param   integer $user_id  Company ID.
    * @param   integer $jobid  Job ID.
    * @return  array   Summary info.
    *
    * @since   3.3
    */
   public function getOneJobSummary($user_id = 0, $jobid = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');
      $datetype = JRequest::getString('datetype', '');

      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $jobid = !empty($jobid) ? $jobid : JRequest::getInt('jobid', 0);
      $xtypes = array('resume', 'event', 'offer', 'entry');

      $db = PFactory::getDbo();

      $summary['date'] = $this->getDateSummary($start, $end, $datetype, $user_id, $jobid);

      // get status summary total
      $summary['status']['total'] = array(
         'resume'      => 0,
         'processed'   => 0,
         'unprocessed' => 0
      );
      $query = "SELECT COUNT(DISTINCT email) AS count, 'resume' AS xtype FROM #__jobs_report_resume WHERE user_id=" . $user_id . " AND date_added>=" . $db->quote($start) . " AND date_added<=" . $db->quote($end) . " AND date_recorded>=" . $db->quote($start) . " AND date_recorded<=" . $db->quote($end) . " AND job_id=" . intval($jobid) . "
                UNION ALL
                SELECT COUNT(DISTINCT email) AS count, 'processed' AS xtype FROM
                   (SELECT jrr.job_id, jrr.resume_id, jrr.email, jrr.processed FROM #__jobs_report_resume AS jrr
                    LEFT JOIN #__jobs_report_resume AS jrr1 ON (jrr1.resume_id = jrr.resume_id AND jrr.id < jrr1.id 
                                                                AND jrr1.date_added >=" . $db->quote($start) . " AND jrr1.date_added <=" . $db->quote($end) . "
                                                                AND jrr1.date_recorded >=" . $db->quote($start) . " AND jrr1.date_recorded <=" . $db->quote($end) . ")
                    WHERE jrr1.resume_id IS NULL AND jrr.user_id=" . $user_id . " 
                          AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . "
                          AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . ") AS r WHERE r.processed=1 AND r.job_id=" . intval($jobid) . "
                UNION ALL
                SELECT SUM(count) AS count, xtype FROM #__jobs_report_jobdetails WHERE gtype IN ('status', 'interview') AND user_id=" . $user_id . " AND date>=" . $db->quote($start) . " AND date<=" . $db->quote($end) . " AND job_id=" . intval($jobid) . " GROUP BY xtype";
      $db->setQuery($query);
      $summary['status']['total'] = array_merge($summary['status']['total'], $db->loadAssocList('xtype', 'count'));
      $summary['status']['total']['unprocessed'] = $summary['status']['total']['resume'] - $summary['status']['total']['processed'];
      JArrayHelper::toInteger($summary['status']['total']);

      $summary['prostatus']['total']['processed']['count']   = $summary['status']['total']['processed'];
      $summary['prostatus']['total']['unprocessed']['count'] = $summary['status']['total']['unprocessed'];
      $prosum = $summary['prostatus']['total']['processed']['count'] + $summary['prostatus']['total']['unprocessed']['count'];
      $summary['prostatus']['total']['processed']['weight']   = ($prosum > 0 ? round(($summary['prostatus']['total']['processed']['count']/$prosum)*100, 1) : 0) . '%';
      $summary['prostatus']['total']['unprocessed']['weight'] = ($prosum > 0 ? round(($summary['prostatus']['total']['unprocessed']['count']/$prosum)*100, 1) : 0) . '%';

      if ($prosum > 0) {
         $summary['prostatus']['pie']['data'] = array(array('已处理简历', $summary['prostatus']['total']['processed']['count']), array('未处理简历', $summary['prostatus']['total']['unprocessed']['count']));
         $summary['prostatus']['pie']['labels'] = array(
            $summary['prostatus']['total']['processed']['count'] . ' (' . $summary['prostatus']['total']['processed']['weight'] . ')',
            $summary['prostatus']['total']['unprocessed']['count'] . ' (' . $summary['prostatus']['total']['unprocessed']['weight'] . ')'
         );
      }

      list($source, $resumeParse) = $this->getResumeDetailsData($start, $end, $user_id, $jobid);
      $summary['source'] = $source;
      $summary['resumeParse'] = $resumeParse;

      return $summary;
   }

   /**
    * Get company subaccount list reports.
    *
    * @return  array  Subaccount array list
    *
    * @since   3.3
    */
   public function getSubaccountList($user_id = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');

      if (empty($start) || empty($end)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $config = PFactory::getConfig();
      $statime = PFactory::getDate($start, $config->get('offset'))->toSql();
      $endtime = PFactory::getDate($end . ' 23:59:59', $config->get('offset'))->toSql();

      $db = PFactory::getDbo();

      // get subaccount list
      $query = "SELECT DISTINCT ur.uid, ur.displayname, GROUP_CONCAT(ur.xtype) AS xtypes, GROUP_CONCAT(ur.count) AS counts FROM
                ((
                   SELECT u.uid, u.displayname, ue.createDate, COUNT(DISTINCT rjj.job_id) AS count, 'job' AS xtype FROM *PREFIX*users_extended AS ue
                   LEFT JOIN *PREFIX*users AS u ON u.uid=ue.uid
                   LEFT JOIN #__jobs_report_jobmanager AS rjm ON rjm.uid=u.uid
                   LEFT JOIN (SELECT job_id, system FROM #__jobs_report_job AS rjj0 WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON (rjj.system=0 AND IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id))
                   WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ")
                   GROUP BY rjm.uid)
                UNION ALL (
                   SELECT u.uid, u.displayname, ue.createDate, COUNT(DISTINCT jrr.email) AS count, 'resume' AS xtype FROM *PREFIX*users_extended AS ue
                   LEFT JOIN *PREFIX*users AS u ON u.uid=ue.uid
                   LEFT JOIN #__jobs_report_jobmanager AS rjm ON rjm.uid=u.uid
                   LEFT JOIN (SELECT user_id, job_id, system FROM #__jobs_report_job AS rjj0 WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id)
                   LEFT JOIN #__jobs_report_resume AS jrr ON (jrr.job_id=rjj.job_id AND jrr.user_id=rjj.user_id)
                   WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ")
                   AND jrr.date_added>=" . $db->quote($start) . " AND jrr.date_added<=" . $db->quote($end) . " AND jrr.date_recorded>=" . $db->quote($start) . " AND jrr.date_recorded<=" . $db->quote($end) . "
                   GROUP BY rjm.uid)
                UNION ALL (
                   SELECT u.uid, u.displayname, ue.createDate, COUNT(DISTINCT r.email) AS count, 'processed' AS xtype FROM *PREFIX*users_extended AS ue
                   LEFT JOIN *PREFIX*users AS u ON u.uid=ue.uid
                   LEFT JOIN #__jobs_report_jobmanager AS rjm ON rjm.uid=u.uid
                   LEFT JOIN (SELECT user_id, job_id, system FROM #__jobs_report_job AS rjj0 WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id)
                   LEFT JOIN (SELECT jrr.job_id, jrr.resume_id, jrr.email, jrr.processed FROM #__jobs_report_resume AS jrr
                              LEFT JOIN #__jobs_report_resume AS jrr1 ON (jrr1.resume_id = jrr.resume_id AND jrr.id < jrr1.id 
                                                                          AND jrr1.date_added >=" . $db->quote($start) . " AND jrr1.date_added <=" . $db->quote($end) . "
                                                                          AND jrr1.date_recorded >=" . $db->quote($start) . " AND jrr1.date_recorded <=" . $db->quote($end) . ")
                              WHERE jrr1.resume_id IS NULL AND jrr.user_id=" . $user_id . " 
                              AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . "
                              AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . ") AS r ON r.job_id=rjj.job_id
                   WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND r.processed=1 GROUP BY rjm.uid)
                UNION ALL (
                   SELECT u.uid, u.displayname, ue.createDate, SUM(jrj.count) AS count, jrj.xtype FROM *PREFIX*users_extended AS ue
                   LEFT JOIN *PREFIX*users AS u ON u.uid=ue.uid
                   LEFT JOIN #__jobs_report_jobmanager AS rjm ON rjm.uid=u.uid
                   LEFT JOIN (SELECT user_id, job_id, system FROM #__jobs_report_job WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON (rjj.system=0 AND IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id))
                   LEFT JOIN #__jobs_report_jobdetails AS jrj ON (jrj.job_id=rjj.job_id AND jrj.user_id=ue.owner AND jrj.gtype='interview' AND jrj.date>=" . $db->quote($start) . " AND jrj.date<=" . $db->quote($end) . ")
                   WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND jrj.xtype IS NOT NULL
                   GROUP BY rjm.uid, jrj.xtype)
                   ) AS ur
                   GROUP BY ur.uid ORDER BY ur.createDate DESC";

      $cache = CacheHelper::getCache('app_reports');
      $list = $cache->call(array($this, '_getSubaccountList'), $query);
      CacheHelper::setTriggers($user_id, $cache, array('*PREFIX*users_extended', '*PREFIX*hr_jobmanager', '#__jobs_job', '#__jobs_report_resume', '#__jobs_report_jobdetails'));

      if (!empty($list)) {
         foreach ($list as $j => $l) {
            $xtypes = explode(',', $l['xtypes']);
            $counts = explode(',', $l['counts']);
            if (!empty($l['xtypes'])) {
               foreach ($xtypes as $i => $x) {
                  $list[$j]['total'][$x] = !empty($counts[$i]) ? $counts[$i] : 0;
               }
            }
         }
      }
      
      return $list;
   }

   public function _getSubaccountList($query) {
      $db = PFactory::getDbo();
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList();
   }
   
   /**
    * Get company job summary Reports.
    *
    * @param   integer $user_id  Company ID.
    * @return  array   Job summary info.
    *
    * @since   3.3
    */
   public function getOneSubaccount($user_id = 0, $uid = '') {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');
      $datetype = JRequest::getString('datetype', '');
   
      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }
   
      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }
   
      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }
   
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $uid = !empty($uid) ? $uid : JRequest::getString('uid', '');
   
      $summary = array();
      $summary['details'] = array(
         'interview_setup' => 0,
         'onlinetest_setup' => 0,
         'persontest_setup' => 0
      );
   
      $db = PFactory::getDbo();
      
      // get manage jobs count
      $query = "SELECT COUNT(DISTINCT rjj.job_id) FROM #__jobs_report_jobmanager AS rjm
                LEFT JOIN #__jobs_report_job AS rjj ON (rjj.system=0 AND rjj.user_id=rjm.user_id AND rjj.date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") AND IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id))
                WHERE date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND uid=" . $db->quote($uid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $summary['jobsCount'] = $db->loadResult();
   
      // get subaccount summary bars data
      $setypeSql = array();
      foreach ($summary['details'] as $setype => $v) {
         $setypeSql[] = "SELECT COUNT(DISTINCT rjj.job_id) AS count, " . $db->quote($setype) . " AS setype FROM #__jobs_report_jobmanager AS rjm
                         LEFT JOIN #__jobs_report_job AS rjj ON (rjj.system=0 AND rjj.user_id=rjm.user_id AND rjj.date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") AND IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id))
                         WHERE rjm.user_id=" . $user_id . " AND date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND uid=" . $db->quote($uid) . " AND $setype=1";
      }
      $db->setQuery(implode(' UNION ALL ', $setypeSql));
      $details = $db->loadAssocList('setype', 'count');
      JArrayHelper::toInteger($details);
      $summary['details'] = array_merge($summary['details'], $details);
      $summary['bars']['data'] = array_values($summary['details']);
      $ynum = 5;
      $max = $ynum;
      $amax = max($summary['bars']['data']);
      $max = $max > $amax ? $max : $amax;
      $summary['bars']['tickInterval'] = ceil(($max*1.2)/$ynum);
      $summary['bars']['max'] = $summary['bars']['tickInterval']*$ynum;
      if (!empty($summary['details'])) {
         $setsum = $summary['jobsCount'];
         foreach ($summary['details'] as $setype => $c) {
            $summary['details'][$setype] = array(
               'count' => $c,
               'weight' => ($setsum > 0 ? round(($c/$setsum)*100, 1) : 0) . '%'
            );
         }
      }
      
      // get subaccount status summary total
      $summary['status']['total'] = array(
         'resume'      => 0,
         'processed'   => 0,
         'unprocessed' => 0
      );
      $query = "SELECT COUNT(DISTINCT email) AS count, 'resume' AS xtype FROM #__jobs_report_jobmanager AS rjm
                LEFT JOIN (SELECT * FROM #__jobs_report_job WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id)
                LEFT JOIN #__jobs_report_resume AS jrr ON (jrr.job_id=rjj.job_id AND jrr.user_id=rjj.user_id)
                WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND rjm.uid=" . $db->quote($uid) . "
                AND jrr.date_added>=" . $db->quote($start) . " AND jrr.date_added<=" . $db->quote($end) . " AND jrr.date_recorded>=" . $db->quote($start) . " AND jrr.date_recorded<=" . $db->quote($end) . "
                UNION ALL
                SELECT COUNT(DISTINCT r.email) AS count, IF(r.processed, 'processed', 'unprocessed') AS xtype FROM #__jobs_report_jobmanager AS rjm
                LEFT JOIN (SELECT * FROM #__jobs_report_job WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id)
                LEFT JOIN (SELECT jrr.job_id, jrr.resume_id, jrr.email, jrr.processed FROM #__jobs_report_resume AS jrr
                           LEFT JOIN #__jobs_report_resume AS jrr1 ON (jrr1.resume_id = jrr.resume_id AND jrr.id < jrr1.id 
                                                                       AND jrr1.date_added >=" . $db->quote($start) . " AND jrr1.date_added <=" . $db->quote($end) . "
                                                                       AND jrr1.date_recorded >=" . $db->quote($start) . " AND jrr1.date_recorded <=" . $db->quote($end) . ")
                           WHERE jrr1.resume_id IS NULL AND jrr.user_id=" . $user_id . " 
                           AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . "
                           AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . ") AS r ON r.job_id=rjj.job_id
                WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND rjm.uid=" . $db->quote($uid) . " AND r.processed=1
                UNION ALL
                SELECT SUM(jrj.count) AS count, jrj.xtype FROM #__jobs_report_jobmanager AS rjm
                LEFT JOIN (SELECT * FROM #__jobs_report_job WHERE user_id=". $user_id ." AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded<=" . $db->quote($end) . ") GROUP BY job_id) AS rjj ON (rjj.system=0 AND IF(rjm.job_id = 'all', 1=1, rjj.job_id=rjm.job_id))
                LEFT JOIN #__jobs_report_jobdetails AS jrj ON (jrj.job_id=rjj.job_id AND jrj.user_id=rjm.user_id AND jrj.gtype IN ('status', 'interview') AND jrj.date>=" . $db->quote($start) . " AND jrj.date<=" . $db->quote($end) .")
                WHERE rjm.user_id=" . $user_id . " AND rjm.date=(SELECT MAX(date) FROM #__jobs_report_jobmanager WHERE user_id=" . $user_id . " AND date<=" . $db->quote($end) . ") AND jrj.xtype IS NOT NULL AND rjm.uid=" . $db->quote($uid) . " GROUP BY jrj.xtype";
      $db->setQuery($query);
      $summary['status']['total'] = array_merge($summary['status']['total'], $db->loadAssocList('xtype', 'count'));
      $summary['status']['total']['unprocessed'] = $summary['status']['total']['resume'] - $summary['status']['total']['processed'];
      JArrayHelper::toInteger($summary['status']['total']);
      
      $summary['prostatus']['total']['processed']['count']   = $summary['status']['total']['processed'];
      $summary['prostatus']['total']['unprocessed']['count'] = $summary['status']['total']['unprocessed'];
      $prosum = $summary['prostatus']['total']['processed']['count'] + $summary['prostatus']['total']['unprocessed']['count'];
      $summary['prostatus']['total']['processed']['weight']   = ($prosum > 0 ? round(($summary['prostatus']['total']['processed']['count']/$prosum)*100, 1) : 0) . '%';
      $summary['prostatus']['total']['unprocessed']['weight'] = ($prosum > 0 ? round(($summary['prostatus']['total']['unprocessed']['count']/$prosum)*100, 1) : 0) . '%';
      
      if ($prosum > 0) {
         $summary['prostatus']['pie']['data'] = array(array('已处理简历', $summary['prostatus']['total']['processed']['count']), array('未处理简历', $summary['prostatus']['total']['unprocessed']['count']));
         $summary['prostatus']['pie']['labels'] = array(
            $summary['prostatus']['total']['processed']['count'] . ' (' . $summary['prostatus']['total']['processed']['weight'] . ')',
            $summary['prostatus']['total']['unprocessed']['count'] . ' (' . $summary['prostatus']['total']['unprocessed']['weight'] . ')'
         );
      }
   
      return $summary;
   }

   public function getSubaccountName($user_id = 0, $uid = '') {
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $uid = !empty($uid) ? $uid : JRequest::getString('uid', '');

      $db = PFactory::getDbo();
      $query = "SELECT u.displayname FROM *PREFIX*users AS u
                LEFT JOIN *PREFIX*users_extended AS ue ON ue.uid=u.uid
                WHERE u.uid=" . $db->quote($uid) . " AND ue.owner=" . $user_id;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadResult();
   }

   /**
    * Get company summary contrast reports.
    *
    * @param   integer $user_id  Company ID.
    * @return  array   Summary info.
    *
    * @since   3.3
    */
   public function getSumContrast($user_id = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');
      $tostart = JRequest::getString('tostart', '');
      $toend = JRequest::getString('toend', '');
      $cnsfrom = JRequest::getString('cnsfrom', '');
      $cnsto = JRequest::getString('cnsto', '');
      $datetype = JRequest::getString('datetype', '');

      if (empty($start) || empty($end) || empty($tostart) || empty($toend) || empty($datetype) || empty($cnsfrom) || empty($cnsto)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start == $tostart || $end == $toend) {
         $this->setError('对比时间不能相同');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;

      $summary = array();
      $xtypes = array('job', 'resume', 'event', 'offer', 'entry');

      $db = PFactory::getDbo();

      switch ($datetype) {
         case 'year':
         case 'quarter':
            $summary['lines']['cnstime'] = array($cnsfrom, $cnsto);
            $linesDatas = array($this->getDateLinesData($start, $end, $datetype, $user_id, 0, $xtypes, false, true), $this->getDateLinesData($tostart, $toend, $datetype, $user_id, 0, $xtypes, false, true));
            $summary['lines']['ticks'] = $linesDatas[1]['ticks'];
            foreach ($linesDatas as $linesData) {
               foreach ($linesData['data'] as $x => $data) {
                  $summary['lines']['data'][$x][] = $data;
                  $summary['lines']['total'][$x][] = array_sum($data);
               }
            }
            break;
         case 'month':
            $summary['bars']['ticks'] = array($cnsfrom, $cnsto);
            $total = array($this->getSumContrastTotal($start, $end, $user_id), $this->getSumContrastTotal($tostart, $toend, $user_id));
            foreach ($total as $xdata) {
               foreach ($xdata as $x => $c) {
                  $summary['bars']['data'][$x][] = intval($c);
               }
            }
            break;
         default:
            break;
      }

      if (!empty($summary)) {
         $ynum = 5;
         foreach ($summary as $ctype => $chart) {
            if ($ctype == 'lines') {
               foreach ($chart['data'] as $x => $data) {
                  $max = $ynum;
                  foreach ($data as $d) {
                     $amax = max($d);
                     $max = $max > $amax ? $max : $amax;
                  }
                  $chart['tickInterval'][$x] = ceil(($max*1.1)/$ynum);
                  $chart['max'][$x] = $chart['tickInterval'][$x]*$ynum;
               }
            } else {
               foreach ($chart['data'] as $x => $data) {
                  $max = $ynum;
                  $amax = max($data);
                  $max = $max > $amax ? $max : $amax;
                  $chart['tickInterval'][$x] = ceil(($max*1.2)/$ynum);
                  $chart['max'][$x] = $chart['tickInterval'][$x]*$ynum;
               }
            }
            $summary[$ctype] = $chart;
         }
      }

      return $summary;
   }

   public function getSumContrastTotal($start, $end, $user_id = 0) {
      $total = array(
         'job' => 0,
         'resume' => 0,
         'event' => 0,
         'offer' => 0,
         'entry' => 0
      );

      $db = PFactory::getDbo();
      $jobSql = $this->getJobRangeSql();

      // get summary total
      $query = "SELECT COUNT(DISTINCT job_id) AS count, 'job' AS xtype FROM #__jobs_report_job 
                WHERE system=0 AND user_id=" . $user_id . " AND date_recorded=(SELECT MAX(date_recorded) FROM #__jobs_report_job WHERE user_id=" . $user_id . " AND date_recorded <=" . $db->quote($end) . ")
                AND publish_date >= " . $db->quote($start) . " AND publish_date <= " . $db->quote($end) . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . "
                UNION ALL 
                SELECT COUNT(DISTINCT email) AS count, 'resume' AS xtype FROM 
                   (SELECT jrr.resume_id, jrr.email, jrr.is_trashed FROM #__jobs_report_resume AS jrr
                    LEFT JOIN #__jobs_report_resume AS jrr1 ON jrr1.resume_id = jrr.resume_id AND jrr.id < jrr1.id AND jrr1.date_added >=" . $db->quote($start) . " AND jrr1.date_added <=" . $db->quote($end) . " AND jrr1.date_recorded >=" . $db->quote($start) . " AND jrr1.date_recorded <=" . $db->quote($end) . "
                    WHERE jrr1.resume_id IS NULL AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . ") AS r
                UNION ALL SELECT SUM(count) AS count, xtype FROM #__jobs_report_summary WHERE user_id=" . $user_id . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " AND date >=" . $db->quote($start) . " AND date <= " . $db->quote($end) . " GROUP BY xtype";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $total = array_merge($total, $db->loadAssocList('xtype', 'count'));

      return $total;
   }

   /**
    * Get company source contrast reports.
    *
    * @param   integer $user_id  Company ID.
    * @return  array   Summary info.
    *
    * @since   3.3
    */
   public function getSourceContrast($user_id = 0) {
      $start = JRequest::getString('start', '');
      $end = JRequest::getString('end', '');
      $datetype = JRequest::getString('datetype', '');

      if (empty($start) || empty($end) || empty($datetype)) {
         $this->setError('网络异常，请重试！');
         return false;
      }

      if ($start > $end) {
         $this->setError('开始时间不能大于结束时间');
         return false;
      }

      if (!PFactory::isMasterUser()) {
         $this->setError('无权访问');
         return false;
      }

      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;

      $total = array();
      $summary = array();
      $xtypes = array('event', 'retest', 'offer', 'entry');
      $sources = JRequest::getVar('sources', array('upload', '51job', 'zhilian'), 'GET', 'array');
      $sourceList = array(
         'upload'  => '枇杷派',
         '51job'   => '前程无忧',
         'zhilian' => '智联招聘',
         'lagou'   => '拉勾',
         'jiancai' => '建筑英才'
      );

      $db = PFactory::getDbo();
      $jobSql = $this->getJobRangeSql();

      // get source summary data
      $bars = array();
      $query = "SELECT COUNT(DISTINCT email) AS count, source, 'resume' AS xtype FROM #__jobs_report_resume AS jrr 
                WHERE jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND job_id IN ($jobSql)" : "") . " GROUP BY source
                UNION ALL 
                   SELECT COUNT(DISTINCT jrr.email) AS count, jrr.source, 'event' AS xtype FROM oc_calendar_objects_details ocod
                   LEFT JOIN oc_calendar_interview_events ocie ON ocie.objectid=ocod.objectid
                   LEFT JOIN #__jobs_report_resume jrr ON jrr.resume_id=ocie.jobseekerid
                   LEFT JOIN oc_calendar_objects oco ON oco.id=ocod.objectid
                   LEFT JOIN #__jobs_job jj ON jj.id=ocod.jobid
                   WHERE jj.employer_id=" . $user_id . " AND ocie.objectid IS NOT NULL AND ocod.round=1 AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . " GROUP BY jrr.source
                UNION ALL
                   SELECT COUNT(DISTINCT jrr.email) AS count, jrr.source, 'retest' AS xtype FROM oc_calendar_objects_details ocod
                   LEFT JOIN oc_calendar_interview_events ocie ON ocie.objectid=ocod.objectid
                   LEFT JOIN #__jobs_report_resume jrr ON jrr.resume_id=ocie.jobseekerid
                   LEFT JOIN oc_calendar_objects oco ON oco.id=ocod.objectid
                   LEFT JOIN #__jobs_job jj ON jj.id=ocod.jobid
                   WHERE jj.employer_id=" . $user_id . " AND ocie.objectid IS NOT NULL AND ocod.round=2 AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . " GROUP BY jrr.source
                UNION ALL
                   SELECT COUNT(DISTINCT jrr.email) AS count, jrr.source, 'offer' AS xtype FROM oc_calendar_interview_result ocir
                   LEFT JOIN oc_calendar_objects_details ocod ON ocod.objectid=ocir.last_interview_objectid
                   LEFT JOIN oc_calendar_interview_events ocie ON ocie.objectid=ocod.objectid
                   LEFT JOIN #__jobs_report_resume jrr ON jrr.resume_id=ocie.jobseekerid
                   LEFT JOIN oc_calendar_objects oco ON oco.id=ocod.objectid
                   LEFT JOIN oc_calendar_offer AS ocf on ocf.objectid=ocir.last_offer_objectid
                   LEFT JOIN #__jobs_job jj ON jj.id=ocod.jobid
                   WHERE jj.employer_id=" . $user_id . " AND ocie.objectid IS NOT NULL AND ocf.objectid IS NOT NULL AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . " GROUP BY jrr.source
                UNION ALL
                   SELECT COUNT(DISTINCT jrr.email) AS count, jrr.source, 'entry' AS xtype FROM oc_calendar_interview_result ocir
                   LEFT JOIN oc_calendar_objects_details ocod ON ocod.objectid=ocir.last_interview_objectid
                   LEFT JOIN oc_calendar_interview_events ocie ON ocie.objectid=ocod.objectid
                   LEFT JOIN #__jobs_report_resume jrr ON jrr.resume_id=ocie.jobseekerid
                   LEFT JOIN oc_calendar_objects oco ON oco.id=ocod.objectid
                   LEFT JOIN oc_calendar_entry_feedback AS ocef ON ocef.id=ocod.entry_stat
                   LEFT JOIN oc_calendar_offer AS ocf on ocf.objectid=ocir.last_offer_objectid
                   LEFT JOIN #__jobs_job jj ON jj.id=ocod.jobid
                   WHERE jj.employer_id=" . $user_id . " AND ocie.objectid IS NOT NULL AND ocef.entry_feedback IS NOT NULL AND ocef.entry_feedback !='待入职' AND jrr.user_id=" . $user_id . " AND jrr.date_added >=" . $db->quote($start) . " AND jrr.date_added <=" . $db->quote($end) . " AND jrr.date_recorded >=" . $db->quote($start) . " AND jrr.date_recorded <=" . $db->quote($end) . (!empty($jobSql) ? " AND jrr.job_id IN ($jobSql)" : "") . " GROUP BY jrr.source";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $sourceTotal = $db->loadAssocList();
      if (!empty($sourceTotal)) {
         foreach ($sourceTotal as $s) {
            $total[$s['source']][$s['xtype']] = intval($s['count']);
         }
      }

      foreach ($xtypes as $x) {
         foreach ($sources as $s) {
            if ($x == 'event') {
               $m = empty($total[$s]['resume']) ? 0 : $total[$s]['resume'];
            } elseif ($x == 'entry') {
               $m = empty($total[$s]['offer']) ? 0 : $total[$s]['offer'];
            } else {
               $m = empty($total[$s]['event']) ? 0 : $total[$s]['event'];
            }
            $n = empty($total[$s][$x]) ? 0 : $total[$s][$x];
            $summary['total'][$x][$s]['m'] = $m;
            $summary['total'][$x][$s]['n'] = $n;
            $bars['data'][$x][$s] = empty($m) ? 0 : round(($n/$m)*100);
         }
      }

      foreach ($bars['data'] as $x => $data) {
         arsort($data, SORT_NUMERIC);
         $bars['data'][$x] = $data;
         $summary['bars'][$x]['sources'] = array_keys($data);
         foreach ($data as $s => $v) {
            $summary['bars'][$x]['ticks'][] = $sourceList[$s];
         }
         $summary['bars'][$x]['data'] = array_values($data);
      }

      return $summary;
   }

   /**
    * Get jobgroup folder filter sql.
    *
    * @param   integer $user_id  Company ID.
    * @param   integer $fid  jobgroup folder ID.
    * @return  array   Job filter sql.
    *
    * @since   3.3
    */
   public function getJobRangeSql($user_id = 0, $fid = 0) {
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;
      $fid = !empty($fid) ? $fid : JRequest::getInt('fid', 0);
      if (empty($fid))
         return '';
      $jobSql = '';
      $db = PFactory::getDbo();
      $query = "SELECT data FROM *PREFIX*hr_job_folder WHERE owner=" . intval($user_id) . " AND folder_id=" . intval($fid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $data = json_decode($db->loadResult(), true);
      if (empty($data)) {
         $jobSql = "SELECT hjff.job_id FROM *PREFIX*hr_job_folder_files AS hjff " .
                   " LEFT JOIN *PREFIX*hr_job_folder AS hjf ON hjf.folder_id=hjff.folder_id" .
                   " WHERE hjf.owner=" . intval($user_id) . " AND hjff.folder_id=" . intval($fid);
      } elseif(!empty($data['cityids'])) {
         $stateids = array();
         JArrayHelper::toInteger($data['cityids']);
         foreach ($data['cityids'] as $cityid) {
            $stateid = ProvinceHelper::getMuniProvinceId($cityid);
            if ($stateid) {
               $stateids[] = $stateid;
            }
         }
         $jobSql = "SELECT id FROM #__jobs_job WHERE id_city IN (" . implode(',', $data['cityids']) . ")";
         if (!empty($stateids)) {
            $jobSql .= " OR id_state IN (" . implode(',', $stateids) . ")";
         }
      }

      return $jobSql;
   }

   public function getTotalStats($user_id = 0) {
      $user_id = !empty($user_id) ? $user_id : PFactory::getMasterUser()->id;

      $db = PFactory::getDbo();
      $jobSql = $this->getJobRangeSql();
      $query = "SELECT count(*) AS count, 'resume' AS xtype FROM *PREFIX*hr_jobseeker AS hj
                LEFT JOIN #__jobs_job AS jj ON jj.id=hj.job_id
                WHERE jj.is_trashed=0 AND hj.is_trashed=0 AND hj.company_id=" . intval($user_id) . (!empty($jobSql) ? " AND hj.job_id IN ($jobSql)" : "") . "
                UNION ALL
                SELECT count(*) AS count, 'job' AS xtype FROM #__jobs_job AS jj WHERE jj.is_trashed=0 AND jj.system=0 AND jj.employer_id=" . intval($user_id) . (!empty($jobSql) ? " AND jj.id IN ($jobSql)" : "");
      $db->setQuery(PUtil::replaceOCPrefix($query));
      return $db->loadAssocList('xtype', 'count');
   }
}


